#! /usr/bin/env python
# -*- coding: utf-8 -*-

# *************************************************************
#  Copyright (c) Huoty - All rights reserved
# 
#      Author: Huoty <sudohuoty@gmail.com>
#  CreateTime: 2016-11-14 17:17:51
# *************************************************************

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column
from sqlalchemy.types import String, Integer, CHAR, BIGINT
from sqlalchemy.ext.declarative import declarative_base


BaseModel = declarative_base()


class Blog(BaseModel):
    __tablename__ = 'blog'

    id = Column(CHAR(32), primary_key=True)
    title = Column(String(64), server_default='', nullable=False)
    text = Column(String, server_default='', nullable=False)
    user = Column(CHAR(32), index=True, server_default='', nullable=False)
    create = Column(BIGINT, index=True, server_default='0', nullable=False)


class User(BaseModel):
    __tablename__ = 'user'

    id = Column(CHAR(32), primary_key=True)
    name = Column(String(32), server_default='', nullable=False)
    passwd = Column(String(64), server_default='', nullable=False)


def init_db():
    BaseModel.metadata.create_all(Engine)

def drop_db():
    BaseModel.metadata.drop_all(Engine)


# Script starts from here

if __name__ == "__main__":
    # 初始化数据库连接:
    #engine = create_engine('postgresql://scott:tiger@localhost/mydatabase?charset=utf8')
    engine = create_engine('sqlite:////tmp/test.sqlite.db')
    #engine = create_engine('sqlite:///:memory:', echo=True)  # 基于内存创建数据库，同时数据日志
    
    # 初始化数据库
    BaseModel.metadata.create_all(engine)
        
    # 创建一个数据库会话
    DBSession = sessionmaker(engine)
    # 创建session对象:
    session = DBSession()
    # 创建新对象:
    from uuid import uuid4 as uid
    blog = Blog(id=uid().hex, title='Hello world', text='This is just a beginning', user='admin', create=111111)
    user = User(id=uid().hex, name='admin', passwd='123456')
    # 添加到session:
    session.add(blog)
    session.add(user)
    
    session.add_all([
        User(id=uid().hex),
        Blog(id=uid().hex),
    ])
    
    # 提交即保存到数据库:
    session.commit()

    # 联合查询
    rows = session.query(Blog, Blog.text).join(User, User.name == Blog.user)
    for row in rows:
        print row.text

    # 关闭session:
    session.close()
    
    # 删除数据库
    BaseModel.metadata.drop_all(engine)
